from flask import Blueprint,request,jsonify,render_template
import sqlite3
from db_dto_helper import db_file_path
from db_dto_helper.utils import PgsqlHelper,SQLiteHelper,MysqlHelper,MssqlHelper


conn_api = Blueprint("db_conn",__name__)
url_prefix = "/conn"

"""
PRAGMA foreign_keys = false;

-- ----------------------------
-- Table structure for base_connects
-- ----------------------------
DROP TABLE IF EXISTS "base_connects";
CREATE TABLE "base_connects" (
  "id" INTEGER NOT NULL,
  "dbtype" TEXT,
  "name" TEXT,
  "host" TEXT,
  "port" TEXT,
  "orderNum" INTEGER,
  "user" TEXT,
  "passwd" TEXT,
  "dbname" TEXT,
  PRIMARY KEY ("id")
);

PRAGMA foreign_keys = true;


"""


@conn_api.route("/index")
def conn_index():
    sql = "select *  from base_connects order by OrderNum"
    conn = sqlite3.connect(db_file_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    data_list = [dict(row) for row in rows]
    print(data_list)
    return  render_template("connect.html",data_list=data_list)

@conn_api.route("/list")
def conn_list():
    sql = "select *  from base_connects order by OrderNum"
    conn = sqlite3.connect(db_file_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    data_list = [dict(row) for row in rows]
    return  jsonify(data_list)

@conn_api.route("/create",methods=["post"])
def create():
    edit_id = request.json.get('id')
    dbtype = request.json.get('dbtype')
    name = request.json.get('name')
    host = request.json.get('host')
    port = request.json.get('port')
    user = request.json.get('user')
    passwd = request.json.get('passwd')
    dbname = request.json.get('dbname')
    order_num = request.json.get('orderNum')
    sql = """INSERT INTO  base_connects (id, dbtype,name, host, port, orderNum, user, passwd, dbname) 
    VALUES  (?,?,?,?,?,?,?,?,? );"""
    conn = sqlite3.connect(db_file_path)
    cursor = conn.cursor()
    cursor.execute("select max(id) +1  from base_connects")
    rows = cursor.fetchall()
    row_id =rows[0][0]
    cursor.execute(sql,(1 if row_id is None else row_id,dbtype, name, host, port, order_num, user, passwd, dbname))
    cursor.close()
    conn.commit()
    conn.close()
    return  jsonify({"code":1,"msg": "create ok!" })


@conn_api.route("/update",methods = ["put"])
def update():
    row_id = request.json.get('id')
    dbtype = request.json.get('dbtype')
    name = request.json.get('name')
    host = request.json.get('host')
    port = request.json.get('port')
    user = request.json.get('user')
    passwd = request.json.get('passwd')
    dbname = request.json.get('dbname')
    order_num = request.json.get('orderNum')
    sql = """UPDATE base_connects SET dbtype = ?, name = ?, host = ?, port = ?, orderNum = ?,
     user = ?, passwd = ?, dbname = ? WHERE id = ?;"""
    conn = sqlite3.connect(db_file_path)
    cursor = conn.cursor()
    cursor.execute(sql, (  dbtype, name, host, port, order_num, user, passwd, dbname,row_id))
    cursor.close()
    conn.commit()
    conn.close()
    return jsonify({"code":1,"msg": "update ok!" })


@conn_api.route("/delete",methods = ["post"])
def delete():
    id = request.json.get("id")
    # print(id)
    sql = "delete  from base_connects where id = ?"
    conn = sqlite3.connect(db_file_path)
    cursor = conn.cursor()
    cursor.execute(sql,(id,))
    cursor.close()
    conn.commit()
    conn.close()
    return jsonify({"code":1,"msg": "delete ok!" })


def exec_sql_row(sql,params):
    conn = sqlite3.connect(db_file_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute(sql,params)
    rows = cursor.fetchall()
    cursor.close()
    conn.commit()
    conn.close()
    data_list = [dict(row) for row in rows]
    return data_list



@conn_api.route("/databses",methods = ["post"])
def get_databses():
    host  = request.json.get('host')
    port  = request.json.get('port')
    user  = request.json.get('user')
    passwd  = request.json.get('passwd')
    dbname  = request.json.get('dbname')
    dbtype  = request.json.get('dbtype')

    if dbtype.upper() =="SQLite".upper():
        return jsonify({"code":2,"msg":"SQLite is not support！"})

    if host is None or port is None or user is None or passwd is None:
        return  jsonify({"code":2,"message":'params is null'})
    rows = []
    if dbtype.upper() =="PGSQL".upper():
        db = PgsqlHelper.PgsqlHelper(dbname, user, passwd, host, port)
        rows = db.get_databases()
    if dbtype.upper() =="MYSQL".upper():
        db = MysqlHelper.MysqlHelper(host,port,user,passwd,dbname)
        rows = db.get_databases()
    if dbtype.upper() =="MSSQL".upper():
        db = MssqlHelper.MssqlHelper(host,port,user,passwd,dbname)
        rows = db.get_databases()
    if dbtype.upper() =="SQLite".upper():
        pass
    # print(rows)
    return jsonify(rows)


@conn_api.route("/tables",methods = ["get"])
def get_tables():
    row_id  = request.args.get('id')
    sql = "select *  from base_connects where id = ?"
    data_list = exec_sql_row(sql,(row_id,))
    if len(data_list) == 0:
        return  jsonify({"code":2,"message":'row is not found!'})
    params = data_list[0]
    dbname, dbtype ,host ,port,user,passwd =(params["dbname"], params["dbtype"], params["host"] ,params["port"], params["user"], params["passwd"])
    rows = []
    if dbtype.upper() =="PGSQL".upper():
        db = PgsqlHelper.PgsqlHelper(dbname, user, passwd, host, port)
        rows = db.get_tables()
    if dbtype.upper() =="MYSQL".upper():
        db = MysqlHelper.MysqlHelper(host,port,user,passwd,dbname)
        rows = db.get_tables()
    if dbtype.upper() =="MSSQL".upper():
        db = MssqlHelper.MssqlHelper(host,port,user,passwd,dbname)
        rows = db.get_tables()
    if dbtype.upper() =="SQLite".upper():
        db = SQLiteHelper.SQLiteHelper(host)
        rows = db.get_tables()
    # print(rows)
    return jsonify(rows)

@conn_api.route("/colums",methods = ["post"])
def get_table_colums():
    row_id  = request.json.get('id')
    name  = request.json.get('name')
    # print(f"row_id={row_id},name={name}")
    sql = "select *  from base_connects where id = ?"
    data_list = exec_sql_row(sql,(row_id,))
    if len(data_list) == 0:
        return  jsonify({"code":2,"message":'row is not found!'})
    params = data_list[0]
    dbname, dbtype ,host ,port,user,passwd =(params["dbname"], params["dbtype"], params["host"] ,params["port"], params["user"], params["passwd"])
    rows = []
    if dbtype.upper() =="PGSQL":
        db = PgsqlHelper.PgsqlHelper(dbname, user, passwd, host, port)
        rows = db.get_colum_list(name)
    if dbtype.upper() =="MYSQL".upper():
        db = MysqlHelper.MysqlHelper(host,port,user,passwd,dbname)
        rows = db.get_colum_list(name)
    if dbtype.upper() =="MSSQL".upper():
        db = MssqlHelper.MssqlHelper(host,port,user,passwd,dbname)
        rows = db.get_colum_list(name)
    if dbtype.upper() =="SQLite".upper():
        db = SQLiteHelper.SQLiteHelper(host)
        rows = db.get_colum_list(name)

    # print(rows)
    return jsonify(rows)

@conn_api.route("/columndesc",methods = ["post"])
def update_columnd_esc():
    row_id = request.json.get('id')
    table_name = request.json.get('table_name')
    name = request.json.get('name')
    col_type = request.json.get('col_type')
    desc = request.json.get('desc')
    if desc is None or desc =='':
        return {"code":2,"msg":"column desc must be have value!"}
    # print(f"row_id={row_id},name={name}")
    sql = "select *  from base_connects where id = ?"
    data_list = exec_sql_row(sql, (row_id,))
    if len(data_list) == 0:
        return jsonify({"code": 2, "message": 'row is not found!'})
    params = data_list[0]
    dbname, dbtype, host, port, user, passwd = (params["dbname"], params["dbtype"], params["host"], params["port"], params["user"], params["passwd"])
    rows = []
    if dbtype.upper() == "PGSQL":
        db = PgsqlHelper.PgsqlHelper(dbname, user, passwd, host, port)
        rows = db.update_column_desc(table_name,name,desc)
    if dbtype.upper() == "MYSQL".upper():
        db = MysqlHelper.MysqlHelper(host, port, user, passwd, dbname)
        rows = db.update_column_desc(table_name, name,col_type,desc)
    if dbtype.upper() == "MSSQL".upper():
        db = MssqlHelper.MssqlHelper(host, port, user, passwd, dbname)
        rows = db.update_column_desc(table_name, name,desc)
    if dbtype.upper() == "SQLite".upper():
        return jsonify({"code":2,"msg":"database not support！"})
    return jsonify(rows)


"""
import psycopg2
 
# 数据库连接参数
conn_params = {
    'dbname': 'your_dbname',
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host',  # 通常是 'localhost' 或数据库服务器的IP地址
    'port': 'your_port'   # 默认是5432
}
 


*****************************
import pymysql.cursors
 
# 连接数据库
connection = pymysql.connect(host='your_host',
                             user='your_username',
                             password='your_password',
                             database='your_database_name',
                             cursorclass=pymysql.cursors.DictCursor)
 
try:
    # 创建一个cursor对象来执行SQL语句
    with connection.cursor() as cursor:
        # 例如：执行一个查询
        sql = "SELECT * FROM your_table_name"
        cursor.execute(sql)
        result = cursor.fetchall()  # 获取所有结果或使用fetchone()获取单条结果
        for row in result:
            print(row)
finally:
    # 关闭连接
    connection.close()
"""